﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Runtime.InteropServices;
using Microsoft.Office.Interop.Excel;
using System.Diagnostics;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Reflection;

namespace InfoPick.Lib.Track
{
    public class ExportExcel
    {
        /// <summary>
        /// 根据句柄获取进程id
        /// </summary>
        /// <param name="hwnd"></param>
        /// <param name="ID"></param>
        /// <returns></returns>
        [DllImport("User32.dll", CharSet = CharSet.Auto)]
        public static extern int GetWindowThreadProcessId(IntPtr hwnd, out int ID);

        /// <summary>
        /// 导出
        /// </summary>
        /// <param name="path"></param>
        /// <param name="ddfList"></param>
        /// <param name="odfList"></param>
        /// <param name="gsList"></param>
        public static void Export(string path,string sheet1,List<EGInfo> list1,string sheet2,List<EGInfo> list2)
        {
            Microsoft.Office.Interop.Excel.Application excelApp = null;
            try
            {
                //创建Excel
                excelApp = new Microsoft.Office.Interop.Excel.Application();

                //创建工作簿（WorkBook：即Excel文件主体本身）
                Workbook excelWB = excelApp.Workbooks.Add(System.Type.Missing);
                for (int i = excelWB.Worksheets.Count; i < 2; i++)
                {
                    excelWB.Worksheets.Add();
                }


                Worksheet excelWS = (Worksheet)excelWB.Worksheets[1];
                excelWS.Name = sheet1;
                createPSheet(excelWS, list1);

                excelWS = (Worksheet)excelWB.Worksheets[2];
                excelWS.Name = sheet2;
                createVSheet(excelWS, list2);


                //将其进行保存到指定的路径
                excelWB.SaveAs(path, 18);
                excelWB.Close();
            }
            finally
            {
                //释放进程
                KillAllExcel(excelApp);
            }
        }

        private static bool KillAllExcel(Microsoft.Office.Interop.Excel.Application excelApp)
        {
            try
            {
                if (excelApp != null)
                {
                    //获取该excel进程的pid
                    int excelProcessId = 0;
                    GetWindowThreadProcessId(new IntPtr(excelApp.Hwnd), out excelProcessId);   // convert pointer to process id

                    excelApp.Quit();

                    System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
                    //释放COM组件，其实就是将其引用计数减1   
                    //System.Diagnostics.Process theProc;   

                    if (excelProcessId != 0)
                    {
                        //这里不能直接通过进程id查找，因为前面调用了quit函数，
                        //进程有可能已经正常关闭，如果再用进程ID查找不存在的进程会出现异常
                        var proc = System.Diagnostics.Process.GetProcessesByName("EXCEL")
                            .SingleOrDefault(c => c.Id == excelProcessId);
                        //先关闭图形窗口。如果关闭失败.有的时候在状态里看不到图形窗口的excel了，   
                        //但是在进程里仍然有EXCEL.EXE的进程存在，那么就需要释放它
                        if (proc != null && proc.CloseMainWindow() == false)
                        {
                            proc.Kill();
                        }
                    }
                    excelApp = null;
                    return true;
                }
            }
            catch (Exception e)
            {
                throw new Exception("释放excel资源出错！请保存现有excel应用后在任务管理器中手动停止EXCEL进程！", e);
            }
            return true;
        }

        private static void createPSheet(Worksheet excelWS, List<EGInfo> list1)
        {
            Range range = null;
            #region 一级表头
            string[] headers = new string[] { "原小区", "目标Server", "目标ServerId", "目标小区", "失败次数" };

            char colStart = 'A';
            for (int i = 0; i < headers.Length; i++)
            {
                range = (Range)excelWS.get_Range(string.Format("{0}1", (char)((int)colStart + i)));
                setRangeFormat(range, headers[i]);
                range.Cells.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow);
            }
            #endregion

            //excelWS.Cells.NumberFormat = "@";     
            //如果数据中存在数字类型 可以让它变文本格式显示

            int startRow = 2;

            #region 填入数据
            object[,] objectArray = new object[list1.Count, headers.Length];
            for (int i = 0; i < list1.Count; i++)
            {
                objectArray[i, 0] = list1[i].OrigDistrict;
                objectArray[i, 1] = list1[i].TargDistrictName;
                objectArray[i, 2] = list1[i].TargServerId;
                objectArray[i, 3] = list1[i].TargDistrict;
                objectArray[i, 4] = list1[i].Count;
            }
            #endregion

            #region 调整样式
            range = (Range)excelWS.get_Range("A" + startRow.ToString(), ((char)((int)colStart + headers.Length - 1)).ToString() + (startRow + list1.Count - 1).ToString());

            range.Application.ScreenUpdating = false;
            range.Application.EnableEvents = false;

            range.Value2 = objectArray;

            range.Application.ScreenUpdating = true;
            range.Application.EnableEvents = true;

            range.Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
            range.BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic,
            System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black));

            range = (Range)excelWS.get_Range("A1", ((char)((int)colStart + headers.Length - 1)).ToString() + (startRow + list1.Count - 1).ToString());
            range.Columns.AutoFit();
            #endregion
        }

        private static void createVSheet(Worksheet excelWS, List<EGInfo> list2)
        {
            Range range = null;
            //#region 一级表头
            //range = (Range)excelWS.get_Range("A1");
            //setRangeFormat(range, "原小区", false, false);

            //range = (Range)excelWS.get_Range("B1");
            //setRangeFormat(range, "目标Server", false, false);

            //range = (Range)excelWS.get_Range("C1");
            //setRangeFormat(range, "目标ServerId", false, false);

            //range = (Range)excelWS.get_Range("D1");
            //setRangeFormat(range, "目标小区", false, false);

            //range = (Range)excelWS.get_Range("E1");
            //setRangeFormat(range, "失败次数", false, false);
            //#endregion

            #region 一级表头
            string[] headers = new string[]{"原小区","目标小区","失败次数"};

            char colStart = 'A';
            for (int i = 0; i < headers.Length; i++)
            {
                range = (Range)excelWS.get_Range(string.Format("{0}1", (char)((int)colStart + i)));
                setRangeFormat(range, headers[i]);
                range.Cells.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow);
            }
            #endregion

            //excelWS.Cells.NumberFormat = "@";     
            //如果数据中存在数字类型 可以让它变文本格式显示

            int startRow = 2;

            #region 填入数据
            object[,] objectArray = new object[list2.Count, headers.Length];
            for (int i = 0; i < list2.Count; i++)
            {
                objectArray[i, 0] = list2[i].OrigDistrict;
                objectArray[i, 1] = list2[i].TargDistrict;
                objectArray[i, 2] = list2[i].Count;
            }
            #endregion

            #region 调整样式
            range = (Range)excelWS.get_Range("A" + startRow.ToString(), ((char)((int)colStart + headers.Length - 1)).ToString() + (startRow + list2.Count - 1).ToString());

            range.Application.ScreenUpdating = false;
            range.Application.EnableEvents = false;

            range.Value2 = objectArray;

            range.Application.ScreenUpdating = true;
            range.Application.EnableEvents = true;

            range.Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
            range.BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic,
            System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black));

            range = (Range)excelWS.get_Range("A1", ((char)((int)colStart + headers.Length - 1)).ToString() + (startRow + list2.Count - 1).ToString());
            range.Columns.AutoFit();
            #endregion
        }

        private static void setRangeFormat(Range range, string value, bool bold = true, bool topBorder = true)
        {
            range.Merge(0);
            range.Font.Size = 10;
            range.Font.Underline = false;
            range.Font.Name = "宋体";
            range.Font.Bold = bold;
            range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
            range.Borders.LineStyle = 1;
            range.BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic,
               System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black));
            if (!topBorder)
                range.Borders.get_Item(XlBordersIndex.xlEdgeTop).LineStyle = XlLineStyle.xlLineStyleNone;
            range.Value = value;
        }
    }
}
